<?php
namespace app\index\model;

use app\index\model\GoodsCode as ModelGoodsCode;
use PHPExcel_IOFactory;
use PHPExcel;
use Exception;
use think\Model;
use think\db;

class GoodsCode extends Model{

    /**
     * 获取列表
     */
    public function getList($page, $limit, $code = '', $beginDate = '', $endDate = '',$zhangtao = '110'){
        $where = [];
        if (!empty($code)){
            $where = array_merge($where, ['code'=>$code]);
        } 
        
        if (!empty($beginDate) && !empty($endDate)) {
            return db('goods_code')->where($where)->where('zhangtao',$zhangtao)->whereBetween('ctime', $beginDate .','.$endDate)->limit(($page-1)*$limit, $limit)->select()->toArray();
        } elseif (!empty($beginDate)) {
            $endDate = time();
            return db('goods_code')->where($where)->where('zhangtao',$zhangtao)->whereBetween('ctime', $beginDate .','.$endDate)->limit(($page-1)*$limit, $limit)->select()->toArray();
        } elseif (!empty($endDate)) {
            return db('goods_code')->where($where)->where('zhangtao',$zhangtao)->where('ctime','LT',$endDate)->limit(($page-1)*$limit, $limit)->select()->toArray();
        }
        return db('goods_code')->where($where)->where('zhangtao',$zhangtao)->limit(($page-1)*$limit, $limit)->select()->toArray();
    }

    /**
     * 获取总数量
     */
    public function count($code = '', $beginDate = '', $endDate = ''){
        $where = [];
        if (!empty($code)){
            $where = array_merge($where, ['code'=>$code]);
        } 
        if (!empty($beginDate) && !empty($endDate)) {
            return db('goods_code')->where($where)->whereBetween('ctime', $beginDate .','.$endDate)->count();
        }elseif(!empty($beginDate)) {
            $endDate = time();
            return db('goods_code')->where($where)->whereBetween('ctime', $beginDate .','.$endDate)->count();
        }elseif(!empty($endDate)) {
            return db('goods_code')->where($where)->where('ctime','LT',$endDate)->count();
        }
        return db('goods_code')->where($where)->count();
    }
    
    /**
     * 获取单条数据
     */
    public function findData($id){
        return GoodsCode::get($id)->toArray();
    }

    /**
     * 根据code获取单条数据
     */
    public function findDataByCode($code){
        return db('goods_code')->where(['code'=>$code])->find();
    }

    /**
     * 保存数据
     */
    public function saveData($id, $code, $uname,$zhangtao){
        if (!empty($id)) {
            $goodsCode = GoodsCode::get($id);
            if (empty($goodsCode)) {
                throw Exception('没有查询到数据');
            }
            $ret = $this->findDataByCode($code);
            if (!empty($ret) && $ret['id'] != $id) {
                throw Exception('此库存编码已经存在了');
            }
            $logModel = new ActionLog();
            $oldArray = $newArray = $goodsCode->toArray();
            $newArray['code'] = $code;
            $diff = array_diff_assoc($oldArray, $newArray);
            //数据有差异，变更修改时间
            if (!empty($diff)) {
                $goodsCode->mtime = time();
            }
            $logModel->insertLog($oldArray, $newArray, CHBM, $uname);
        } else {
            $ret = $this->findDataByCode($code);
            if (!empty($ret)) {
                throw Exception('此库存编码已经存在了');
            }
            $goodsCode = $this;
            $goodsCode->ctime = time();
            $goodsCode->mtime = time();
        }
        $goodsCode->code = $code;
        $goodsCode->uname = $uname;
        $goodsCode->zhangtao = $zhangtao;
        $goodsCode->save();
        if (!empty($id)) {
           return GoodsCode::get($id);
        }
        return $goodsCode;
    }

    /**
     * 删除数据
     */
    public function delData($id)
    {
        $goodsCode = GoodsCode::get($id);
        if (empty($goodsCode)) {
            throw Exception('没有查询到数据');
        }
        $goodsCode->delete();    
    }

     /**
     * 删除所有数据
     */
    public function delAll()
    {
        db('goods_code')->delete(true);
        return true;
    }

    /**
     * excel导入数据库
     */
    public function importToDb($info, $uname,$zhangtao){
        $i = 0;
        $ext = $info->getExtension();
        if(!in_array($ext,['xlsx','xls'])){
            throw Exception("文件格式有误!");
        }else{
            $path = '../public/uploads/'.str_replace('\\', '/', $info->getSaveName());   //上传文件路径
            if (!file_exists($path)) {
                throw Exception("上传文件丢失!");
            }

            if ($ext == 'xlsx') {
                $objReader = \PHPExcel_IOFactory::createReader('Excel2007');
                $objPHPExcel = $objReader->load($path, 'utf-8');
            } elseif ($ext == 'xls') {
                $objReader = \PHPExcel_IOFactory::createReader('Excel5');
                $objPHPExcel = $objReader->load($path, 'utf-8');
            }
            // $sheetCount = $objPHPExcel->getSheetCount();//获取sheet工作表总个数
            // $rowData = array();
            // $RowNum = 0;
            // for($i =0;$i <= $sheetCount-1;$i++){//循环sheet工作表的总个数
            //         $sheet = $objPHPExcel->getSheet($i);
            //         $highestRow = $sheet->getHighestRow();
            //         $RowNum += $highestRow-1;//计算所有sheet的总行数
            //         $highestColumn = $sheet->getHighestColumn();
            //         //从第$i个sheet的第1行开始获取数据
            //         for($row = 1;$row <= $highestRow;$row++){
            //             //把每个sheet作为一个新的数组元素 键名以sheet的索引命名 利于后期数组的提取
            //             $rowData[$i][] = $this->arrToOne($sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE));
            //         }
            // }
            // $arr = $this->prepareData($rowData);
            // halt($arr);  //打印数据
            $sheet = $objPHPExcel->getSheet(0);
            $highestRow = $sheet->getHighestRow(); // 取得总行数
    //      $highestColumn = $sheet->getHighestColumn(); // 取得总列数
            $importRows = 0;
            for ($j = 0; $j <= $highestRow; $j++) {
                $importRows++;
                $code = (string)$objPHPExcel->getActiveSheet()->getCell("A$j")->getValue();

                if(!empty($code)){
                    $ret = $this->findDataByCode($code);
                    if (empty($ret)) {
                        $addData = ['code'=>$code, 'uname'=>$uname, 'ctime'=>time(), 'mtime'=>time(),'zhangtao'=>$zhangtao];
                        db('goods_code')->insert($addData);
                        $i++;                    
                    }
                }
            }
        }
        return $i;
    }

    private function arrToOne($Array){ 
        $arr = array(); 
        foreach ($Array as $key => $val) { 
            if( is_array($val) ) { 
                $arr = array_merge($arr, $this->arrToOne($val)); 
            } else { 
                $arr[] = $val; 
            } 
        } 
        return $arr; 
    }

    private function prepareData($Array){
        $newArray = array();
        $i = 0;
        foreach($Array as $key => $item){
            $index = 0;
            //判断第一行有没有存货编码
            $flipArray = array_flip(array_filter($item[0]));
            if(array_key_exists('存货编码', $flipArray)){
                $i++;
                $index = $flipArray['存货编码'];
                foreach($item as $k => $v){
                    if($v[$index] != '存货编码'){
                        $newArray[$i][$k-1] = $v[$index];
                    }
                }
            }
        }
        return $newArray;
    }

}
?>